<?php

class m_service extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }
    
    function get_count_service_pagination($params) {
        $sql = "SELECT COUNT(a.services_id)'total' FROM services a
                JOIN barang b ON a.barang_id = b.barang_id
                WHERE b.barang_noinventaris LIKE '".$params[0]."' 
                AND b.barang_nama LIKE '".$params[1]."' 
                AND YEAR(a.services_tgl) LIKE '".$params[2]."'";
        if (!empty($params[3])){
            $sql .=" AND MONTH(a.services_tgl) = ".$params[3];
        }
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }
    
    function get_all_service_pagination($params) {
        $sql = "SELECT a.*, b.barang_nama, b.barang_noinventaris FROM services a
                JOIN barang b ON a.barang_id = b.barang_id
                WHERE b.barang_noinventaris LIKE '".$params[0]."' 
                AND b.barang_nama LIKE '".$params[1]."' 
                AND YEAR(a.services_tgl) LIKE '".$params[2]."'";
        if (!empty($params[3])){
            $sql .=" AND MONTH(a.services_tgl) = ".$params[3];
        }
            $sql .=" ORDER BY a.services_id DESC
                LIMIT ".$params[4].",".$params[5];
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_detail_service_by_id($params) {
        $sql = "SELECT a.*, b.barang_nama, b.barang_noinventaris FROM services a
                JOIN barang b ON a.barang_id =b.barang_id
                WHERE a.services_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_year_service() {
        $sql = "SELECT YEAR(services_tgl)'tahun' FROM services 
                GROUP BY YEAR(services_tgl)
                ORDER BY YEAR(services_tgl) ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_last_services_id() {
        $sql = "SELECT services_id FROM services ORDER BY services_id DESC LIMIT 0,1";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['services_id'];
        } else {
            return "";
        }
    }

    // insert
    function insert($params) {
        // update by this date
        $sql = "INSERT INTO services(services_tgl, services_desc, services_tglselesai, 
                                      services_status, barang_id, services_parts, 
                                      services_biaya, services_keterangan, 
                                      mdb, mdd) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
        return $this->db->query($sql, $params);
    }
    
    function update_file($param) {
        // update by this date
        $sql = "UPDATE services SET services_file = ? WHERE services_id = ?";
        return $this->db->query($sql, $param);
    }
    
    // update
    function update($params) {
        // update by this date
        $sql = "UPDATE services SET services_tgl = ?, services_desc = ?, services_tglselesai = ?,
                services_status = ?, barang_id = ?, services_parts = ?, services_biaya = ?,
                services_keterangan = ?, mdb = ?, mdd = NOW() WHERE services_id = ?";
        return $this->db->query($sql, $params);
    }
    
    // delete
    function delete($params) {
        // update by this date
        $sql = "DELETE FROM services WHERE services_id = ?";
        return $this->db->query($sql, $params);
    }

}

